package com.typingtutor.dto;

import com.typingtutor.db.DatabaseManager;
import com.typingtutor.model.FieldKeyInfo;
import com.typingtutor.model.FieldMapping;
import com.typingtutor.model.Word;
import org.apache.commons.lang3.StringUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.StringJoiner;

public class FieldMappingDAO {
    private static volatile FieldMappingDAO INSTANCE;
    private FieldMappingDAO() {
        // 私有构造函数，防止实例化
    }
    public static FieldMappingDAO getInstance() {
        if (INSTANCE == null) {
            synchronized (FieldMappingDAO.class) {
                if (INSTANCE == null) {
                    INSTANCE = new FieldMappingDAO();
                }
            }
        }
        return INSTANCE;
    }

    public void deleteById(String fieldMappingId) {
        String deleteRelationSql = "DELETE FROM field_key_mapping_relation WHERE field_mapping_id = ?;";
        String deleteFieldMappingSql = "DELETE FROM field_mapping WHERE id = ?;";
        String deleteFieldKeySql = "DELETE FROM field_key_info WHERE id = ?;";
        Connection connection = null;
        try {
            connection = DatabaseManager.getConnection();
            connection.setAutoCommit(false);

            // 先删除关联表中的记录
            try (PreparedStatement relationPstmt = connection.prepareStatement(deleteRelationSql)) {
                relationPstmt.setString(1, fieldMappingId);
                relationPstmt.executeUpdate();
            }

            // 再删除 field_mapping 表中的记录
            try (PreparedStatement fieldMappingPstmt = connection.prepareStatement(deleteFieldMappingSql)) {
                fieldMappingPstmt.setString(1, fieldMappingId);
                fieldMappingPstmt.executeUpdate();
            }
            try (PreparedStatement fieldKeyPstmt = connection.prepareStatement(deleteFieldKeySql)) {
                fieldKeyPstmt.setString(1, fieldMappingId);
                fieldKeyPstmt.executeUpdate();
            }
            connection.commit();
        } catch (SQLException e) {
            if (connection != null) {
                try {
                    connection.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
        } finally {
            if (connection != null) {
                try {
                    connection.setAutoCommit(true);
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public List<String> findDisplayFieldMappingId(String fieldKeyIds) {
        StringJoiner joiner = new StringJoiner(",");
        for (String string : fieldKeyIds.split(",")) {
            joiner.add("'" + string + "'");
        }
        String selectSql = "SELECT field_mapping_id FROM field_key_mapping_relation WHERE field_key_id IN(" + joiner.toString() + ")";
        try (Connection connection = DatabaseManager.getConnection()) {
            PreparedStatement pstmt = connection.prepareStatement(selectSql);
            ResultSet rs = pstmt.executeQuery();
            List<String> displayFieldMappingIds = new ArrayList<>();
            while (rs.next()) {
                displayFieldMappingIds.add(rs.getString("field_mapping_id"));
            }
            return displayFieldMappingIds;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return Collections.emptyList();
    }

    public FieldMapping findByRandomIdAndFieldKeyIsChineseAndDocumentId(String randomId, String documentId) {
        String selectSql = "SELECT fm.* " +
                "FROM folder_random_rules frr " +
                "JOIN folder_rule_field_key_relation frfkr ON frr.id = frfkr.folder_random_rules_id " +
                "JOIN field_key_info fki ON frfkr.field_key_info_id = fki.id " +
                "JOIN field_key_mapping_relation fkmr ON fki.id = fkmr.field_key_id " +
                "JOIN field_mapping fm ON fkmr.field_mapping_id = fm.id " +
                "WHERE frr.id = ? " +
                "  AND fki.field_key = '中文含义' AND fm.document_id=?; ";
        try (Connection connection = DatabaseManager.getConnection()) {
            PreparedStatement pstmt = connection.prepareStatement(selectSql);
            pstmt.setString(1, randomId);
            pstmt.setString(2, documentId);
            ResultSet rs = pstmt.executeQuery();
            FieldMapping fieldMapping = null;
            while (rs.next()) {
               fieldMapping = new FieldMapping(rs.getString("id"), rs.getString("field_value"), rs.getString("document_id"));
            }
            return fieldMapping;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public Word findFieldValueByFieldKeyIdAndFieldMappingId(String fieldKeyId, String fieldMappingId) {
        String selectSql = "SELECT " +
                "    fm.* " +
                "FROM " +
                "    field_key_mapping_relation fkmr " +
                "INNER JOIN " +
                "    field_mapping fm ON fkmr.field_mapping_id = fm.id " +
                "INNER JOIN " +
                "    field_mapping fm2 ON fm.document_id = fm2.document_id AND fm2.id = ? " +
                "WHERE " +
                "    fkmr.field_key_id = ?;";
        try (Connection connection = DatabaseManager.getConnection()) {
            PreparedStatement pstmt = connection.prepareStatement(selectSql);
            pstmt.setString(1, fieldMappingId);
            pstmt.setString(2, fieldKeyId);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                return new Word(rs.getString("id")
                , rs.getString("field_value")
                , rs.getString("document_id"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<FieldMapping> findById(String fieldMappingId) {
        String sql = "SELECT * FROM field_mapping WHERE id = ?;";
        try (Connection connection = DatabaseManager.getConnection()) {
            PreparedStatement pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, fieldMappingId);
            ResultSet rs = pstmt.executeQuery();
            List<FieldMapping> fieldMappings = new ArrayList<>();
            while (rs.next()) {
                fieldMappings.add(new FieldMapping(rs.getString("id"),
                        rs.getString("field_value"),
                        rs.getString("document_id")));
            }
            return fieldMappings;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return Collections.emptyList();
    }

    public void updateTypingUIDisplay(String fieldKey, boolean display) {
        String updateSql = "UPDATE field_key_info SET `display` = ? WHERE field_key = ?;";
        try (Connection connection = DatabaseManager.getConnection()) {
            PreparedStatement pstmt = connection.prepareStatement(updateSql);
            pstmt.setBoolean(1, display);
            pstmt.setString(2, fieldKey);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void deleteAllCards() {
        try (Connection conn = DatabaseManager.getConnection();
             Statement stmt = conn.createStatement()) {
            stmt.executeUpdate("DELETE FROM field_mapping");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
